
Imports DAL
Imports MyClasses
Imports System.Configuration
Imports System.Data.SqlClient

Public Class DSetFinYear
    Public Shared FinYrId As Integer

    Public Shared Function SaveYear(ByVal FinYearStartDT As Date, ByVal FinYearEndDT As Date) As Integer
        Dim dbConnection As New dbAccess
        Dim intResult As Integer = 0
        Try
            dbConnection.BeginTransaction()
            intResult = Updateyears(FinYearStartDT, FinYearEndDT, dbConnection)
            If intResult = 0 Then Exit Function
            dbConnection.CommitTransaction()
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DSetFinYear", "SetFinancialYear", "SaveYear")
            intResult = 0
        Finally
            If intResult = 0 Then
                dbConnection.RollBackTransaction()
                SaveYear = intResult
            Else : SaveYear = intResult
            End If
            dbConnection.closeSqlcon()
        End Try
    End Function

    Public Shared Function Updateyears(ByVal FinYearStartDT As Date, ByVal FinYearEndDT As Date, ByRef dbConnection As dbAccess) As Integer
        Dim strInsrtSQL As String = String.Empty
        Dim strUpdtSQL As String = String.Empty
        Dim intResult As Integer = 0
        Dim parm(8) As SqlParameter
        Dim NewFinYear As String = String.Empty
        Try
            strUpdtSQL = " UPDATE TFINYEAR SET STATUS = 'X', WORKING_STATUS = 'X' " & _
                         " WHERE FirmId = " & ConfigurationManager.AppSettings("FirmID") & "" & _
                         " AND WORKING_STATUS = 'A' "
            intResult = dbConnection.ExecuteNonQuery(strUpdtSQL)
            If intResult > 0 Then
                strInsrtSQL = " INSERT INTO TFINYEAR (FinYrId, FirmId, fin_start_dt, fin_end_dt, fin_year_label, " & _
                              " Status, Working_Status, LU, LD)" & _
                              " VALUES (@FinYrId, @FirmId, @fin_start_dt, @fin_end_dt, @fin_year_label, " & _
                              " @Status, @Working_Status, @LU, @LD)"

                FinYrId = DGeneral.GetSeqNum("FinYrId", dbConnection, True)
                NewFinYear = "FY " & FinYearStartDT.Year & "-" & FinYearEndDT.Year
                parm(0) = New SqlParameter("@FinYrId", FinYrId)
                parm(1) = New SqlParameter("@FirmId", ConfigurationManager.AppSettings("FirmID"))
                parm(2) = New SqlParameter("@fin_start_dt", FinYearStartDT)
                parm(3) = New SqlParameter("@fin_end_dt", FinYearEndDT)
                parm(4) = New SqlParameter("@fin_year_label", NewFinYear)
                parm(5) = New SqlParameter("@Status", "A")
                parm(6) = New SqlParameter("@Working_Status", "A")
                parm(7) = New SqlParameter("@LU", CCommon.GetUserID)
                parm(8) = New SqlParameter("@LD", Now.Date())
                intResult = 0
                intResult = dbConnection.ExecuteNonQuery(strInsrtSQL, parm)
            End If
            Return intResult
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DSetFinYear", "SetFinancialYear", "Updateyears")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetStock() As DataTable
        Dim strSQL As String = String.Empty
        Try
            strSQL = " SELECT * FROM tStock WHERE Quantity > 0 Order By ItemID, CLRcode"

            Return DbHelper.ExecuteDataTable(strSQL)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DSetFinYear", "SetFinancialYear", "GetStock")
            Return Nothing
        End Try
    End Function

    Public Shared Function SetNewYrStock(ByRef dtbGetStock As DataTable) As Integer
        Dim dbConnection As New dbAccess
        Dim intStockResult As Integer = 0
        Dim intStockSumResult As Integer = 0
        Dim dtDStockSum As DataTable = New DataTable
        Try
            dbConnection.BeginTransaction()

            intStockResult = SetNewYrStock(dtbGetStock, dbConnection)
            If intStockResult = 0 Then Exit Function

            dtDStockSum = GetStockSummary()
            If dtDStockSum.Rows.Count > 0 Then
                intStockSumResult = SetNewYrStockSum(dtDStockSum, dbConnection)
                If intStockSumResult = 0 Then Exit Function
            End If

            dbConnection.CommitTransaction()
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DSetFinYear", "SetFinancialYear", "SetNewYrStock")
            intStockResult = 0
        Finally
            If intStockResult = 0 Then
                dbConnection.RollBackTransaction()
                SetNewYrStock = intStockResult
            Else : SetNewYrStock = intStockResult
            End If
            dbConnection.closeSqlcon()
        End Try
    End Function

    Public Shared Function SetNewYrStock(ByVal dtbGetStock As DataTable, ByRef dbConnection As dbAccess) As Integer
        Dim strStockSQL As String = String.Empty
        Dim strTopngStockSQL As String = String.Empty
        Dim intResult As Integer = 0
        Dim intCount As Integer = 0
        Dim StockID As Integer = 0
        Dim parm(27) As SqlParameter
        Dim param(17) As SqlParameter
        Try
            For intCount = 0 To dtbGetStock.Rows.Count - 1
                StockID = DGeneral.GetSeqNum("STOCK", dbConnection, True)
                strStockSQL = " INSERT INTO tStock(StockID,ItemID,CLRcode,MfgDate,ExpDate,MRP," & _
                                 " BillRate,PurchasePrice,SalePrice1,SalePrice2,SellPack,SellUnit," & _
                                 " PurPack,PurUnit,Quantity,LocationID,ConversionFactor,SchemeID," & _
                                 " schemeItemQty,SchemeDiscount,FreeReplacement,BarCode,InvoiceID,VendorID,Status,LU,LD,FinYrID)" & _
                                 " VALUES(@StockID,@ItemID,@CLRcode,@MfgDate,@ExpDate,@MRP,@BillRate,@PurchasePrice," & _
                                 " @SalePrice1,@SalePrice2,@SellPack,@SellUnit,@PurPack,@PurUnit,@Quantity," & _
                                 " @LocationID,@ConversionFactor,@SchemeID,@schemeItemQty,@SchemeDiscount," & _
                                 " @FreeReplacement,@BarCode,@InvoiceID,@VendorID,@Status,@LU,@LD,@FinYrID)"
                With dtbGetStock.Rows(intCount)
                    parm(0) = New SqlParameter("@StockID", StockID)
                    parm(1) = New SqlParameter("@ItemID", .Item(1))
                    parm(2) = New SqlParameter("@CLRcode", .Item(2))
                    parm(3) = New SqlParameter("@MfgDate", .Item(3))
                    parm(4) = New SqlParameter("@ExpDate", .Item(4))
                    parm(5) = New SqlParameter("@MRP", .Item(5))
                    parm(6) = New SqlParameter("@BillRate", .Item(6))
                    parm(7) = New SqlParameter("@PurchasePrice", .Item(7))
                    parm(8) = New SqlParameter("@SalePrice1", .Item(8))
                    parm(9) = New SqlParameter("@SalePrice2", .Item(9))
                    parm(10) = New SqlParameter("@SellPack", .Item(10))
                    parm(11) = New SqlParameter("@SellUnit", .Item(11))
                    parm(12) = New SqlParameter("@PurPack", .Item(12))
                    parm(13) = New SqlParameter("@PurUnit", .Item(13))
                    parm(14) = New SqlParameter("@Quantity", .Item(14))
                    parm(15) = New SqlParameter("@LocationID", .Item(15))
                    parm(16) = New SqlParameter("@ConversionFactor", .Item(16))
                    parm(17) = New SqlParameter("@SchemeID", .Item(17))
                    parm(18) = New SqlParameter("@schemeItemQty", .Item(18))
                    parm(19) = New SqlParameter("@SchemeDiscount", .Item(19))
                    parm(20) = New SqlParameter("@FreeReplacement", .Item(20))
                    parm(21) = New SqlParameter("@BarCode", .Item(21))
                    parm(22) = New SqlParameter("@InvoiceID", .Item(22))
                    parm(23) = New SqlParameter("@VendorID", .Item(23))
                    parm(24) = New SqlParameter("@Status", .Item(24))
                    parm(25) = New SqlParameter("@LU", CCommon.GetUserID)
                    parm(26) = New SqlParameter("@LD", Now.Date())
                    parm(27) = New SqlParameter("@FinYrID", FinYrId)

                    intResult = intResult + dbConnection.ExecuteNonQuery(strStockSQL, parm)

                    strTopngStockSQL = " INSERT INTO tOpeningStock(OsID,ItemID,Item_Unit,ItemsPerPack,CLRcode," & _
                            " VendorID,ManufacuredDate,ExpDate,MRP,PurchaseRate,Qty,SchemeID,schemeItemQty," & _
                            " SchemeDiscount,FreeReplacement,Remark,FinYrID,OpeningDate)" & _
                            " VALUES(@OsID,@ItemID,@Item_Unit,@ItemsPerPack,@CLRcode," & _
                            " @VendorID,@ManufacuredDate,@ExpDate,@MRP,@PurchaseRate,@Qty,@SchemeID,@schemeItemQty," & _
                            " @SchemeDiscount,@FreeReplacement,@Remark,@FinYrID,@OpeningDate)"

                    param(0) = New SqlParameter("@OsID", DGeneral.GetSeqNum("OSTOCK", dbConnection, True))
                    param(1) = New SqlParameter("@ItemID", .Item(1))
                    param(2) = New SqlParameter("@Item_Unit", .Item(11))
                    param(3) = New SqlParameter("@ItemsPerPack", .Item(16))
                    param(4) = New SqlParameter("@CLRcode", .Item(2))
                    param(5) = New SqlParameter("@VendorID", .Item(23))
                    param(6) = New SqlParameter("@ManufacuredDate", .Item(3))
                    param(7) = New SqlParameter("@ExpDate", .Item(4))
                    param(8) = New SqlParameter("@MRP", .Item(5))
                    param(9) = New SqlParameter("@PurchaseRate", .Item(7))
                    param(10) = New SqlParameter("@Qty", .Item(14))
                    param(11) = New SqlParameter("@SchemeID", .Item(17))
                    param(12) = New SqlParameter("@schemeItemQty", .Item(18))
                    param(13) = New SqlParameter("@SchemeDiscount", .Item(19))
                    param(14) = New SqlParameter("@FreeReplacement", .Item(20))
                    param(15) = New SqlParameter("@Remark", "")
                    param(16) = New SqlParameter("@FinYrID", FinYrId)
                    param(17) = New SqlParameter("@OpeningDate", Now.Date())

                    intResult = intResult + dbConnection.ExecuteNonQuery(strTopngStockSQL, param)
                End With
            Next
            Return intResult
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DSetFinYear", "SetFinancialYear", "SetNewYrStock")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetStockSummary() As DataTable
        Dim strSQL As String = String.Empty
        Try
            strSQL = " SELECT * FROM tStockSummary Order By ItemID"

            Return DbHelper.ExecuteDataTable(strSQL)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DSetFinYear", "SetFinancialYear", "GetStockSummary")
            Return Nothing
        End Try
    End Function

    Public Shared Function SetNewYrStockSum(ByVal dtDStockSum As DataTable, ByRef dbConnection As dbAccess) As Integer
        Dim strSQL As String = String.Empty
        Dim intResult As Integer = 0
        Dim intCount As Integer = 0
        Dim param(13) As SqlParameter
        Try
            For intCount = 0 To dtDStockSum.Rows.Count - 1
                strSQL = " INSERT INTO tStockSummary (StockSumID, ItemID, OpeningStock, CurrentStock, SchemStock, " & _
                         " GodownStock, BrekageStock, ExpiredStock, LastSaleBill, mFctrID, LastPurchaseInvoice, " & _
                         " LU, LD, FinYrID)" & _
                         " VALUES (@StockSumID, @ItemID, @OpeningStock, @CurrentStock, @SchemStock, @GodownStock," & _
                         " @BrekageStock, @ExpiredStock, @LastSaleBill, @mfctrID, @LastPurchaseInvoice, @LU, @LD, @FinYrID)"
                With dtDStockSum.Rows(intCount)
                    param(0) = New SqlParameter("@StockSumID", DGeneral.GetSeqNum("STOCKSUM", dbConnection, True))
                    param(1) = New SqlParameter("@ItemID", .Item(1))
                    param(2) = New SqlParameter("@OpeningStock", .Item(2))
                    param(3) = New SqlParameter("@CurrentStock", .Item(3))
                    param(4) = New SqlParameter("@SchemStock", .Item(4))
                    param(5) = New SqlParameter("@GodownStock", .Item(5))
                    param(6) = New SqlParameter("@BrekageStock", .Item(6))
                    param(7) = New SqlParameter("@ExpiredStock", .Item(7))
                    param(8) = New SqlParameter("@LastSaleBill", .Item(8))
                    param(9) = New SqlParameter("@mfctrID", .Item(9))
                    param(10) = New SqlParameter("@LastPurchaseInvoice", .Item(10))
                    param(11) = New SqlParameter("@LU", CCommon.GetUserID)
                    param(12) = New SqlParameter("@LD", Now.Date())
                    param(13) = New SqlParameter("@FinYrID", FinYrId)
                End With
                intResult = intResult + dbConnection.ExecuteNonQuery(strSQL, param)
            Next
            Return intResult
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DSetFinYear", "SetFinancialYear", "SetNewYrStockSum")
            Return Nothing
        End Try
    End Function
End Class



